package dao;


import java.lang.reflect.Field;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class database {


    private final String sConnStr = "jdbc:mysql://127.0.0.1:3306/swim?useUnicode=true";


    private String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
    private List list;
    public Connection connect = null;
    public ResultSet rs = null;
    public String user = "root";
    public String passwd = "123456";

    public database() {
        try {

            String dbDriver = "com.mysql.jdbc.Driver";
            Class.forName(dbDriver).newInstance();
        } catch (Exception ex) {
            System.out.println("出错");
        }
    }


    public ResultSet executeQuery(String sql) {
        try {
            connect = DriverManager.getConnection(sConnStr, user, passwd);
            Statement stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
        return rs;
    }


    public int update_execute(String sql) {

        int i = 0;
        try {

            connect = DriverManager.getConnection(sConnStr, user, passwd);
            Statement stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            i = stmt.executeUpdate(sql);
            if (i == 1) {
                return i;
            } else {
                return i;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 1;
        }
    }

    public synchronized static String getID() {

        Random random = new Random();
        StringBuffer ret = new StringBuffer(20);
        java.util.Date date = new java.util.Date();
        SimpleDateFormat format = new SimpleDateFormat("MMddHHmmss", java.util.Locale.CHINA);
        ret.append(format.format(date));
        String rand = String.valueOf(Math.abs(random.nextInt()));
        //ret.append(getDateStr());
        ret.append(rand.substring(0, 4));

        return ret.toString();
    }

    public static int getBetweenDayNumber(String dateA, String dateB) {
        long dayNumber = 0;
        //1小时=60分钟=3600秒=3600000
        long mins = 60L * 1000L;
        //long day= 24L * 60L * 60L * 1000L;计算天数之差
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        try {
            java.util.Date d1 = df.parse(dateA);
            java.util.Date d2 = df.parse(dateB);
            dayNumber = (d2.getTime() - d1.getTime()) / mins;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return (int) dayNumber;
    }


    public List<HashMap> select(String sql) {
        System.out.println(sql);
        List<HashMap> list = new ArrayList();
        try {
            connect = DriverManager.getConnection(sConnStr, user, passwd);
            Statement stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);

            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();

            while (rs.next()) {
                HashMap map = new HashMap();
                int i = rsmd.getColumnCount();
                for (int j = 1; j <= i; j++) {
                    if (!rsmd.getColumnName(j).equals("ID")) {
                        String str = rs.getString(j) == null ? "" : rs.getString(j);
                        if (str.equals("null")) str = "";
                        map.put(rsmd.getColumnName(j), str);
                    } else
                        map.put("id", rs.getString(j));
                }
                list.add(map);
            }
            rs.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block

            if (sql.equals("show tables"))
                list = select("select table_name from   INFORMATION_SCHEMA.tables");
            else
                e.printStackTrace();
        }
        return list;
    }

    public List<HashMap> select(String sql, int pageno, int rowsize) {
        List<HashMap> list = new ArrayList<HashMap>();
        List<HashMap> mlist = new ArrayList<HashMap>();
        try {
            list = this.select(sql);
            int min = (pageno - 1) * rowsize;
            int max = pageno * rowsize;

            for (int i = 0; i < list.size(); i++) {

                if (!(i < min || i > (max - 1))) {
                    mlist.add(list.get(i));
                }
            }
        } catch (RuntimeException re) {
            re.printStackTrace();
            throw re;
        }


        return mlist;
    }


    public synchronized static String ensubStr(Object source, int length) {
        String msource = database.filterStrIgnoreCase(source.toString(), "<", ">");
        if (msource.length() > length) {
            msource = msource.substring(0, length) + "...";
        }
        return msource;
    }

    public static String filterStrIgnoreCase(String source, String from, String to) {
        String sourceLowcase = source.toLowerCase();
        String sub1, sub2, subLowcase1, subLowcase2;
        sub1 = sub2 = subLowcase1 = subLowcase2 = "";
        int start = 0, end;
        boolean done = true;
        if (source == null) return null;
        if (from == null || from.equals("") || to == null || to.equals(""))
            return source;
        while (done) {
            start = sourceLowcase.indexOf(from, start);
            if (start == -1) {
                break;
            }
            subLowcase1 = sourceLowcase.substring(0, start);
            sub1 = source.substring(0, start);
            end = sourceLowcase.indexOf(to, start);
            if (end == -1) {
                end = sourceLowcase.indexOf("/>", start);
                if (end == -1) {
                    done = false;
                }
            } else {
                end = end + to.length();
                subLowcase2 = sourceLowcase.substring(end, source.length());
                sub2 = source.substring(end, source.length());
                sourceLowcase = subLowcase1 + subLowcase2;
                source = sub1 + sub2;
            }
            //System.out.println(start+" "+end);
        }
        return source;
    }


    /**
     * 根据传入的带下划线的字符串转化为驼峰格式
     *
     * @param str
     * @return
     */
    public static Pattern UNDERLINE_PATTERN = Pattern.compile("_([a-z])");

    public static String underlineToHump(String str) {
        //正则匹配下划线及后一个字符，删除下划线并将匹配的字符转成大写

        Matcher matcher = UNDERLINE_PATTERN.matcher(str);
        StringBuffer sb = new StringBuffer(str);
        if (matcher.find()) {
            sb = new StringBuffer();
            //将当前匹配的子串替换成指定字符串，并且将替换后的子串及之前到上次匹配的子串之后的字符串添加到StringBuffer对象中
            //正则之前的字符和被替换的字符
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
            //把之后的字符串也添加到StringBuffer对象中
            matcher.appendTail(sb);
        } else {
            //去除除字母之外的前面带的下划线
            return sb.toString().replaceAll("_", "");
        }
        return underlineToHump(sb.toString());
    }


    /**
     * str转date方法
     */
    public static java.sql.Date StringToDate(String sDate) {

        String str = sDate;
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date d = null;
        try {
            d = format.parse(str);
        } catch (Exception e) {
            e.printStackTrace();
        }
        java.sql.Date date = new java.sql.Date(d.getTime());
        return date;
    }

    /**
     * 将一个表的字段放在list里面
     *
     * @param rs 数据库查询结果
     * @return hashmap结果
     */
    public static List<String> getTableField(ResultSet rs) {
        List<String> hm = new ArrayList<>();
        ResultSetMetaData rsmd = null;
        try {
            if (rs.next()) {
                rsmd = rs.getMetaData();
                int count = rsmd.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    String key = rsmd.getColumnLabel(i);
                    hm.add(key);
                }
            }
            rs.first();
        } catch (SQLException e) {
            System.out.println("未查询到数据");
        }

        return hm;
    }

    /**
     * 数据库查询结果放在一个hashmap里面
     *
     * @param rs 数据库查询结果
     * @return hashmap结果
     */
    public static Map<String, String> getResultMap(ResultSet rs) {
        Map<String, String> hm = new HashMap<String, String>();
        ResultSetMetaData rsmd = null;
        try {
            rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            for (int i = 1; i <= count; i++) {
                String key = rsmd.getColumnLabel(i);
                String value = rs.getString(i);
                hm.put(key, value);
            }
        } catch (SQLException e) {
            System.out.println("获取查询属性失败");
        }
        return hm;
    }

    /**
     * 数据库查询结果放在一个hashmap list里面 ，和上面的差不多
     *
     * @param rs 数据库查询结果
     * @return hashmap list
     */
    public static List<Map<String, String>> getResultMapList(ResultSet rs) {
        Map<String, String> hm;
        List<Map<String, String>> result = new ArrayList<>();
        ResultSetMetaData rsmd = null;
        try {
            rs.first();
            do {
                hm = new HashMap<String, String>();
                rsmd = rs.getMetaData();
                int count = rsmd.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    String key = rsmd.getColumnLabel(i);
                    String value = rs.getString(i);
                    hm.put(key, value);
                }
                result.add(hm);
            }
            while (rs.next());

        } catch (SQLException e) {
            System.out.println("获取查询属性失败");
        }
        return result;
    }

    /**
     * 从resultset的结果封装成实体类
     *
     * @param u         查询对象类型
     * @param resultSet 查询结果
     * @return 封装好的实体类
     */
    public static Object selectOneObject(Object u, ResultSet resultSet) {


        Map<String, String> pojo_name2db_name = new HashMap<>();
        Map<String, String> db_field_name;
        List<String> table_fields;

        table_fields = database.getTableField(resultSet);
        //如果没有数据，直接返回null
        if (table_fields.size() == 0) {
            return null;
        }
        for (String fieldName : table_fields
        ) {
            pojo_name2db_name.put(database.underlineToHump(fieldName), fieldName);
        }

        db_field_name = database.getResultMap(resultSet);

        try {
            return cap(u, pojo_name2db_name, db_field_name);

        } catch (InstantiationException e) {
            System.out.println("构建出错");
        } catch (IllegalAccessException e) {
            System.out.println("访问权限受控");
        }

        return null;
    }

    /**
     * 从resultset的结果封装成实体类，得到的对象是一个list
     *
     * @param u         查询对象类型
     * @param resultSet 查询结果
     * @return 封装好的实体类
     */
    public static List<Object> selectList(Object u, ResultSet resultSet) {

        long tmpLong;
        Double tmpDouble;

        database d = new database();

        Map<String, String> pojo_name2db_name = new HashMap<>();
        Map<String, String> db_field_name;
        List<String> table_fields;
        List<Object> resultList = new ArrayList<>();
        List<Map<String, String>> selected_fromDB;

        table_fields = database.getTableField(resultSet);
        //如果没有数据，直接返回null
        if (table_fields.size() == 0) {
            return null;
        }
        for (String fieldName : table_fields
        ) {
            pojo_name2db_name.put(database.underlineToHump(fieldName), fieldName);
        }

        selected_fromDB = database.getResultMapList(resultSet);
        try {
            return cap(u, pojo_name2db_name, selected_fromDB);
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }

        return resultList;
    }

    public static Object cap(Object u, Map<String, String> pojo_name2db_name, Map<String, String> db_field_name) throws InstantiationException, IllegalAccessException {
        long tmpLong;
        Double tmpDouble;
        Object o = u.getClass().newInstance();
        Field[] fields = o.getClass().getDeclaredFields();
        for (Field field : fields) {
            try {
                //反射对象在使用时抑制Java语言访问检查(私有也可以访问)，使得可以获取属性值
                field.setAccessible(true);
                //在这里取数据库查询出来的值
//                        System.out.println(field.getType());
                if (field.getType().toString().equals("long")) {
                    if (db_field_name.get(pojo_name2db_name.get(field.getName())) != null) {
                        tmpLong = Integer.parseInt(db_field_name.get(pojo_name2db_name.get(field.getName())));
                        field.set(o, tmpLong);
                    }
                }
                if (field.getType().toString().equals("double")) {
                    if (db_field_name.get(pojo_name2db_name.get(field.getName())) != null) {
                        tmpDouble = Double.parseDouble(db_field_name.get(pojo_name2db_name.get(field.getName())));
                        field.set(o, tmpDouble);
                    }
                }
                if (field.getType().toString().equals("class java.sql.Date")) {
                    if (db_field_name.get(pojo_name2db_name.get(field.getName())) != null) {
                        field.set(o, StringToDate(db_field_name.get(pojo_name2db_name.get(field.getName()))));
                    }
                }
                if (field.getType().toString().equals("class java.lang.String")) {
                    if (db_field_name.get(pojo_name2db_name.get(field.getName())) != null) {
                        field.set(o, db_field_name.get(pojo_name2db_name.get(field.getName())));
                    }
                }
//                            field.set(o, test.get(pojo_name2db_name.get(field.getName())));
                field.setAccessible(false);
            } catch (Exception e) {
                System.out.println("设置字段值失败");
            }
        }

        return o;
    }

    public static List<Object> cap(Object u, Map<String, String> pojo_name2db_name, List<Map<String, String>> selected_fromDB) throws InstantiationException, IllegalAccessException {

        Field[] fields = u.getClass().getDeclaredFields();

        List<Object> resultList = new ArrayList<>();
        selected_fromDB.forEach(item -> {
            Object o = null;
            try {
                o = u.getClass().newInstance();
                for (Field field : fields) {

                    try {
                        //反射对象在使用时抑制Java语言访问检查(私有也可以访问)，使得可以获取属性值
                        field.setAccessible(true);
                        //在这里取数据库查询出来的值

                        if (field.getType().toString().equals("long")) {
                            if (item.get(pojo_name2db_name.get(field.getName())) != null) {
                                long tmpLong = Integer.parseInt(item.get(pojo_name2db_name.get(field.getName())));
                                field.set(o, tmpLong);
                            }
                        }
                        if (field.getType().toString().equals("double")) {
                            if (item.get(pojo_name2db_name.get(field.getName())) != null) {
                                Double tmpDouble = Double.parseDouble(item.get(pojo_name2db_name.get(field.getName())));
                                field.set(o, tmpDouble);
                            }
                        }
                        if (field.getType().toString().equals("class java.sql.Date")) {
                            if (item.get(pojo_name2db_name.get(field.getName())) != null) {
                                field.set(o, StringToDate(item.get(pojo_name2db_name.get(field.getName()))));
                            }
                        }
                        if (field.getType().toString().equals("class java.lang.String")) {
                            if (item.get(pojo_name2db_name.get(field.getName())) != null) {
                                field.set(o, item.get(pojo_name2db_name.get(field.getName())));
                            }
                        }
//                            field.set(o, test.get(pojo_name2db_name.get(field.getName())));
                        field.setAccessible(false);


                    } catch (Exception e) {
                        System.out.println("设置字段值失败");
                    }
                }
            } catch (InstantiationException e) {
                throw new RuntimeException(e);
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }


            resultList.add(o);
        });


        return resultList;
    }


}



